package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.shsh.safemanage.model.Admin;
import com.shsh.safemanage.model.PostCheck;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.DaoUtil;

public class UserDao {
	public User login(String jobNum, String password) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM],"
				+ "employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE,"
				+ "employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME,"
				+ "employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME,"
				+ "[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID,"
				+ "employee.[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED], department3.ROLE_ID "
				+ "FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "left outer join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID "
				+ "left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID "
				+ "where JOB_NUM=? and (LOGIN_PASSWORD=? or LOGIN_PASSWORD is null) and IS_REGISTERED=1";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, jobNum);
			pstm.setString(2, password);

			ResultSet rs = pstm.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				user.setRoleID(rs.getLong("ROLE_ID"));
			}

			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<User> getUserListByDepartment1ID(Long depart1ID) {
		String sql = "select e.*, d3.DEPARTMENT3_NAME, d3.ROLE_ID, ro.ROLE_LEVEL, ro.ROLE_TYPE, ro.ROLE_NAME "
				+ " from employee e left outer join department3 as d3 on d3.ID = e.DEPARTMENT3_ID "
				+ " left outer join [role] as ro on ro.ID = d3.ROLE_ID " + " where e.DEPARTMENT1_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<User> uesrList = new ArrayList<User>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);

			ResultSet rs = pstm.executeQuery();
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setRoleID(rs.getLong("ROLE_ID"));
				user.setRoleType(rs.getInt("ROLE_TYPE"));

				uesrList.add(user);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return uesrList;
	}

	public List<User> getUserListByDepartment2ID(Long depart2ID) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM],"
				+ "employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE,"
				+ "employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME,"
				+ "employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME,"
				+ "[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID,"
				+ "employee.[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] "
				+ "FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "left outer join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID "
				+ "left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID where employee.DEPARTMENT2_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<User> uesrList = new ArrayList<User>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart2ID);

			ResultSet rs = pstm.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));

				uesrList.add(user);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return uesrList;
	}

	public User getUserByID(Long id) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM],"
				+ "employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE,"
				+ "employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME,"
				+ "employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME,"
				+ "[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID,"
				+ "employee.[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] "
				+ "FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "left outer join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID "
				+ "left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID where employee.ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
			}

			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public boolean hasBeenRegistered(String jobNum) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			String sql = "select IS_REGISTERED from employee where JOB_NUM=?";

			pstm = conn.prepareStatement(sql);
			pstm.setString(1, jobNum);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getBoolean("IS_REGISTERED");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public User getUser(String name, String jobNum) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM],"
					+ "employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE,"
					+ "employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME,"
					+ "employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME,"
					+ "[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID,"
					+ "employee.[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] "
					+ "FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
					+ "left outer join department2 on employee.DEPARTMENT2_ID=department2.ID "
					+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
					+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID "
					+ "left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID ";
			String sql1 = null;

			if (jobNum == null || jobNum.length() == 0) {
				if (name == null || name.length() == 0) {
					return null;
				} else {
					sql1 = "where employee.NAME=?";
					pstm = conn.prepareStatement(sql + sql1);
					pstm.setString(1, name);
				}
			} else {
				sql1 = "where employee.JOB_NUM=?";
				pstm = conn.prepareStatement(sql + sql1);
				pstm.setString(1, jobNum);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				user.setRegistered(rs.getBoolean("IS_REGISTERED"));

				rs.close();

				if (user.getJobPost2ID() != null) {
					sql = "select POST_NAME from job_post where ID=?";
					pstm = conn.prepareStatement(sql);
					pstm.setLong(1, user.getJobPost2ID());

					rs = pstm.executeQuery();
					if (rs.next()) {
						user.setJobPost2Name(rs.getString(1));
					}
					rs.close();

					if (user.getJobPost3ID() != null) {
						sql = "select POST_NAME from job_post where ID=?";
						pstm = conn.prepareStatement(sql);
						pstm.setLong(1, user.getJobPost3ID());

						rs = pstm.executeQuery();
						if (rs.next()) {
							user.setJobPost3Name(rs.getString(1));
						}
						rs.close();
					}
				}

				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public int getUserTitleLevel(Long titleLevelID) {
		String sql = "select TITLE_LEVEL from job_title where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, titleLevelID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt("TITLE_LEVEL");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}

	public Admin adminLogin(String adminName, String password) {
		String sql = "select * from admin where ADMIN_NAME=? and ADMIN_PW=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, adminName);
			pstm.setString(2, password);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return new Admin(adminName, password);
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public boolean updateUserInfo(User registerUser) {
		if (registerUser == null) {
			return false;
		}

		String isRegisterSQL = "select ID, IS_REGISTERED from employee where JOB_NUM=?";
		String sql = "update employee set DEPARTMENT1_ID=?, DEPARTMENT2_ID=?, DEPARTMENT3_ID=?, LOGIN_PASSWORD=?, IS_REGISTERED=?, NAME=? "
				+ "where ID=?";
		String insertSQL = "insert into employee(NAME, JOB_NUM, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_ID, IS_REGISTERED, LOGIN_PASSWORD) "
				+ "values(?, ?, ?, ?, ?, ?, ?) select @@identity as USER_ID";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement insertPstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(isRegisterSQL);
			ResultSet rs = null;
			if (registerUser.getJobNum() != null) {
				pstm.setString(1, registerUser.getJobNum());
				rs = pstm.executeQuery();
			}
			if (rs != null && rs.next()) {
				pstm = conn.prepareStatement(sql);

				registerUser.setId(rs.getLong("ID"));
				pstm.setLong(1, registerUser.getDepartment1ID());
				pstm.setLong(2, registerUser.getDepartment2ID());
				pstm.setLong(3, registerUser.getDepartment3ID());
				pstm.setString(4, registerUser.getPassword());
				pstm.setBoolean(5, Boolean.TRUE);
				pstm.setString(6, registerUser.getName());
				pstm.setLong(7, registerUser.getId());

				pstm.executeUpdate();

				return true;
			} else {
				insertPstm = conn.prepareStatement(insertSQL);

				insertPstm.setString(1, registerUser.getName());
				insertPstm.setString(2, registerUser.getJobNum());
				insertPstm.setLong(3, registerUser.getDepartment1ID());
				insertPstm.setLong(4, registerUser.getDepartment2ID());
				insertPstm.setLong(5, registerUser.getDepartment3ID());
				insertPstm.setBoolean(6, Boolean.TRUE);
				insertPstm.setString(7, registerUser.getPassword());

				insertPstm.executeUpdate();
				ResultSet rs1 = insertPstm.getGeneratedKeys();
				if (rs1.next()) {
					registerUser.setId(rs1.getLong("USER_ID"));
				}

				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean addUser(User user) {
		String sql = "insert into employee(NAME, PERSON_NUM, JOB_NUM, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_ID, "
				+ "JOB_POST1_ID, JOB_POST2_ID, JOB_POST3_ID, JOB_TITLE_ID, MANAGE_GROUP_ID, LOGIN_PASSWORD, IS_REGISTERED) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setString(1, user.getName());
			pstm.setString(2, user.getPersonNum());
			pstm.setString(3, user.getJobNum());
			pstm.setLong(4, user.getDepartment1ID());
			pstm.setLong(5, user.getDepartment2ID());
			pstm.setLong(6, user.getDepartment3ID());
			pstm.setLong(7, user.getJobPost1ID());
			pstm.setLong(8, user.getJobPost2ID());
			pstm.setLong(9, user.getJobPost3ID());
			pstm.setLong(10, user.getJobTitleID());
			pstm.setLong(11, user.getManageGroupID());
			pstm.setString(12, "");
			pstm.setBoolean(13, Boolean.TRUE);

			pstm.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public List<User> getManageGroupMappingUserList(Long manageGroupID, Long depart1ID) {
		List<User> userList = new ArrayList<User>();

		String sql = "select ID from department2 where DEPARTMENT1_ID=? and MANAGE_GROUP_ID=?";
		String sql1 = "select ID, NAME, JOB_POST1_ID, JOB_POST2_ID, JOB_POST3_ID, DEPARTMENT3_ID from employee where DEPARTMENT2_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(sql1);

			pstm.setLong(1, depart1ID);
			pstm.setLong(2, manageGroupID);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Long depart2ID = rs.getLong("ID");

				pstm1.setLong(1, depart2ID);
				ResultSet rs1 = pstm1.executeQuery();
				while (rs1.next()) {
					User user = new User();
					user.setId(rs1.getLong("ID"));
					user.setName(rs1.getString("NAME"));
					user.setJobPost1ID(rs1.getLong("JOB_POST1_ID"));
					user.setJobPost2ID(rs1.getLong("JOB_POST2_ID"));
					user.setJobPost3ID(rs1.getLong("JOB_POST3_ID"));
					user.setDepartment3ID(rs1.getLong("DEPARTMENT3_ID"));

					userList.add(user);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userList;
	}

	public List<User> getUsersByRoleType(Long department1ID, int roleType) {
		String sql = "select employee.ID, NAME, employee.DEPARTMENT1_ID, employee.DEPARTMENT2_ID, employee.DEPARTMENT3_ID, ROLE_TYPE "
				+ "from employee inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join role on department3.ROLE_ID=role.ID where employee.DEPARTMENT1_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<User> userList = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);
			pstm.setInt(2, roleType);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setRoleType(rs.getInt("ROLE_TYPE"));

				userList.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userList;
	}

	public boolean updateUserOpenRegister(Long userID) {
		String sql = "update employee set IS_REGISTERED = 0 where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public Map<String, Object> getUsersByIsRegistered(int pageNum, boolean isRegistered) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM],"
				+ "employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE,"
				+ "employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME,"
				+ "employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME,"
				+ "[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID,"
				+ "employee.[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] "
				+ "FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "left outer join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID "
				+ "left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID " + "where employee.IS_REGISTERED="
				+ (isRegistered ? "1" : "0") + " ";

		String paginSQL = DaoUtil.getPagingSQL(sql, pageNum, Constants.PAGE_SIZE, "JOB_NUM", true);
		int count = DaoUtil.getCount(sql, new int[] {}, new Object[] {});

		Connection conn = null;
		PreparedStatement pstm = null;
		List<User> userList = new ArrayList<User>();
		Map<String, Object> daoMap = new HashMap<String, Object>();
		daoMap.put(Constants.DAO_RESULT_NUMBER, count);
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(paginSQL);

			ResultSet rs = pstm.executeQuery();
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPassword(rs.getString("LOGIN_PASSWORD"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));

				userList.add(user);
			}

			daoMap.put(Constants.DAO_RESULT, userList);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return daoMap;
	}

	public boolean deleteUserById(Long userID) {
		String sql = "delete from employee where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			conn.setAutoCommit(false);

			pstm.setLong(1, userID);

			pstm.executeUpdate();

			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public PostCheck[] getPostcheckMappingByUserID(long userID) {
		String sql = "select p.ID, p.CHECK_NAME from user_postcheck_mapping as ump "
				+ "inner join postcheck as p on ump.POSTCHECK_ID = p.ID " + "where ump.USER_ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<PostCheck> lst = new ArrayList<PostCheck>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostCheck postcheck = new PostCheck();
				postcheck.setId(rs.getLong("ID"));
				postcheck.setCheckName(rs.getString("CHECK_NAME"));

				lst.add(postcheck);
			}

			return lst.toArray(new PostCheck[lst.size()]);
		} catch (Exception e) {
			e.printStackTrace();
			return new PostCheck[] {};
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}
}
